package com.sh.data.engine.domain.shims.mysql.manager;

import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Date;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/5 18:05
 */
public class MysqlManager extends GenericDbManager {

    public MysqlManager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "com.mysql.cj.jdbc.Driver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT VERSION()";
    }

    @Override
    public boolean hasSchema() {
        return false;
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        List<FieldInfoDomain> fieldList = getFieldList(dbName, tableName);
        String fieldType = "";
        for (FieldInfoDomain i : fieldList) {
            if (Objects.equals(fieldName, i.getFieldName())) {
                fieldType = i.getFieldType();
                if ("varchar".equalsIgnoreCase(fieldType)) {
                    String columnSize = i.getColumnSize().toString();
                    fieldType = String.format("%s(%s)", fieldType, columnSize);
                }
                break;
            }
        }
        String sql =
            String.format(
                "ALTER TABLE %s.%s MODIFY COLUMN %s %s COMMENT '%s'",
                dbName, tableName, fieldName, fieldType, fieldDes);
        doAlertFiledDesc(sql);
        return true;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where TABLE_TYPE = 'BASE TABLE' AND table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        return getTableList(dbName);
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);

        if (set.contains("TABLE_NAME")) {
            String tblName = rs.getString("TABLE_NAME");
            entity.setTableName(tblName);
        }

        if (set.contains("TABLE_TYPE")) {
            String tableType = rs.getString("TABLE_TYPE");
            TableInfoDomain.TblType tblType = null;
            switch (tableType) {
                case "BASE TABLE":
                    tblType = TableInfoDomain.TblType.MANAGED_TABLE;
                    break;
                case "VIEW":
                    tblType = TableInfoDomain.TblType.VIEW;
                    break;
            }
            entity.setTblType(tblType);
        }

        // 视图的create_time和update_time都是空的
        if (set.contains("UPDATE_TIME")) {
            Date updateTime = rs.getDate("UPDATE_TIME");
            if (null == updateTime && set.contains("CREATE_TIME")) {
                updateTime = rs.getDate("CREATE_TIME");
            }
            if (null != updateTime) {
                entity.setLastDdlTime(updateTime.getTime());
            }
        }

        if (set.contains("TABLE_COMMENT")) {
            final String tableComment = rs.getString("TABLE_COMMENT");
            entity.setTableComment(tableComment);
        }

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where TABLE_TYPE = 'VIEW' AND table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        return getViewList(dbName);
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        return getTableAndViewList(dbName);
    }

    private static String columnNameToFieldName(String columnName, boolean capitalizeNext) {
        StringBuilder fieldNameBuilder = new StringBuilder();


        for (char c : columnName.toCharArray()) {
            if (c == '_') {
                capitalizeNext = true;
            } else {
                if (capitalizeNext) {
                    fieldNameBuilder.append(Character.toUpperCase(c));
                    capitalizeNext = false;
                } else {
                    fieldNameBuilder.append(Character.toLowerCase(c));
                }
            }
        }
        return fieldNameBuilder.toString();
    }

    @SneakyThrows
    public List<Object> queryObject(String sql, String className,
                                    Object... params) {
        List<Object> resultList = new ArrayList<>();
        // 根据类名反射创建对象实例
        Connection connection = this.getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置SQL参数
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            // 执行查询
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Class<?> clazz = Class.forName(className);
                    Object object = clazz.newInstance();
                    // 设置对象的属性值
                    // 这里假设类中的属性名称与数据库中的列名相同，你可能需要更复杂的映射逻辑
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object columnValue = resultSet.getObject(i);
                        String fieldName = "set" + columnNameToFieldName(columnName, true);

                        columnName = columnNameToFieldName(columnName, false);
                        // 这里假设有相应的setter方法，你需要确保类中有相应的setter方法
                        Field field = object.getClass().getDeclaredField(columnName);
                        field.setAccessible(true);
                        Method setterMethod = object.getClass()
                            .getMethod(fieldName, field.getType());
                        if (columnValue != null) {
                            // 如果参数类型与 columnValue 类型不匹配，则尝试进行类型转换
                            if (!field.getType().isAssignableFrom(columnValue.getClass())) {
                                columnValue = convertToType(columnValue, field.getType());
                            }
                            setterMethod.invoke(object, columnValue);
                        }
                    }
                    resultList.add(object);
                }
            }
        }
        return resultList;
    }

    private Object convertToType(Object columnValue, Class<?> targetType) {
        if (targetType.equals(String.class)) {
            return columnValue.toString();
        } else if (targetType.equals(Integer.class) || targetType.equals(int.class) || targetType.equals(Short.class)) {
            return Integer.parseInt(columnValue.toString());
        } else if (targetType.equals(Long.class) || targetType.equals(long.class)) {
            return Long.parseLong(columnValue.toString());
        } else if (targetType.equals(Double.class) || targetType.equals(double.class)) {
            return Double.parseDouble(columnValue.toString());
        } else if (targetType.equals(Float.class) || targetType.equals(float.class)) {
            return Float.parseFloat(columnValue.toString());
        } else if (targetType.equals(Boolean.class) || targetType.equals(boolean.class)) {
            return Boolean.parseBoolean(columnValue.toString());
        } else if (targetType.equals(java.util.Date.class)) {
            // 时间类型的解析，假设 columnValue 是字符串形式的时间
            return Timestamp.valueOf((LocalDateTime) columnValue);
        } else {
            // 如果无法转换，则抛出异常或者返回 null，具体处理方式根据需求而定
            throw new IllegalArgumentException("Cannot convert columnValue to targetType: " + targetType);
        }
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String tableName) throws SQLException {
        //8.0之前的版本，表名带.的时候，需要加上``，8.0版本的表名如果加``,会导致查不到字段列表
        if (!tableName.contains(dbOptions.getDatabase().getEscape())
            && !StringUtils.contains(getDbVersion(), "8")) {
            tableName = escapeTableName(tableName);
        }
        return super.getFieldList(dbName, tableName);
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException {
        //8.0之前的版本，表名带.的时候，需要加上``，8.0版本的表名如果加``,会导致查不到字段列表
        if (!tableName.contains(dbOptions.getDatabase().getEscape())
            && !StringUtils.contains(getDbVersion(), "8")) {
            tableName = escapeTableName(tableName);
        }
        return super.getFieldList(dbName, schema, tableName);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where table_schema = ? AND TABLE_NAME = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        return getTableInfoByTableName(dbName, tableName);
    }

    public static void main(String... args) throws SQLException {
        String s = columnNameToFieldName("is_support_rtf", true);
        System.out.println(s);
//    DbOptions dbOptions = new DbOptions();
//    dbOptions.setUrl("jdbc:mysql://10.88.36.168:3306/hufu?useSSL=false");
//    dbOptions.setUsername("root");
//    dbOptions.setPassword("MynewPass@1.");
//    MysqlManager mysqlManager = new MysqlManager(dbOptions);
//
//    List<TableInfoDomain> tableList = mysqlManager.getTableList("hufu");
//
//    System.out.println(tableList);
//
//    final List<FieldInfoDomain> fieldList =
//        mysqlManager.getFieldList("hufu", "ads_data_sync_config");
//    for (FieldInfoDomain fieldInfoDomain : fieldList) {
//      System.out.println(fieldInfoDomain);
//    }
    }
}
